/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dal;

import connection.ConnectionDB;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.CarDetails;

/**
 *
 * @author Nhang Con
 */
public class CarDetailDAL {

    private ConnectionDB conn = new ConnectionDB();
    private CallableStatement cs;
    private ResultSet rs;

    public List<CarDetails> getListAllCar() {
        List<CarDetails> lcar = new ArrayList<CarDetails>();
        CarDetails car;
        try {
            cs = conn.createConnection().prepareCall("{call sp_ListAllCar()}");
            rs = cs.executeQuery();
            while (rs.next()) {
                car = new CarDetails();
                car.setCarID(rs.getInt("CarID"));
                car.setModelID(rs.getInt("ModelID"));
                car.setCarName(rs.getString("CarName"));
                car.setDescription(rs.getString("Description"));
                car.setPrice(rs.getFloat("Price"));
                car.setImages(rs.getString("Images"));
                car.setCapacity(rs.getInt("Capacity"));
                lcar.add(car);

            }
            return lcar;

        } catch (SQLException ex) {
            Logger.getLogger(CarDetailDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lcar;
    }

    public boolean updateCarDetail(CarDetails car) {
        try {
            cs = conn.createConnection().prepareCall("{call sp_UpdateCardetail(?,?,?,?,?,?,?)}");
            cs.setInt("modelid", car.getModelID());
            cs.setInt("carid", car.getCarID());
            cs.setString("carname", car.getCarName());
            cs.setString("des", car.getDescription());
            cs.setString("images", car.getImages());
            cs.setInt("capacity", car.getCapacity());
            cs.setFloat("price", car.getPrice());
            int n = cs.executeUpdate();
            if (n == 1) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(CarDetailDAL.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public boolean createNewCar(CarDetails car) {
        try {
            cs = conn.createConnection().prepareCall("{call sp_CreateCarDetail(?,?,?,?,?,?)}");
            cs.setInt("modelid", car.getModelID());
            cs.setString("carname", car.getCarName());
            cs.setString("des", car.getDescription());
            cs.setString("images", car.getImages());
            cs.setInt("capacity", car.getCapacity());
            cs.setFloat("price", car.getPrice());
            int n = cs.executeUpdate();
            if (n == 1) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(CarDetailDAL.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

    }

    public int getRowInCar() {
        try {
            int rowIntable = 0;
            cs = conn.createConnection().prepareCall("{call sp_getRowInCarDetail()}");
            rs = cs.executeQuery();
            if (rs.next()) {
                rowIntable = rs.getInt("rows");
                return rowIntable;
            }
            return 0;
        } catch (SQLException ex) {
            Logger.getLogger(CarDetailDAL.class.getName()).log(Level.SEVERE, null, ex);
            return 0;
        }
    }

    public List<CarDetails> listCarDetailByPage(int page) {
        List<CarDetails> lcar = new ArrayList<CarDetails>();
        CarDetails car;
        try {

            cs = conn.createConnection().prepareCall("{call sp_ListCarDetailByPage(?)}");
            cs.setInt("indexpage", page);
            rs = cs.executeQuery();
            while (rs.next()) {
                car = new CarDetails();
                car.setCarID(rs.getInt("CarID"));
                car.setModelID(rs.getInt("ModelID"));
                car.setCarName(rs.getString("CarName"));
                car.setDescription(rs.getString("Description"));
                car.setPrice(rs.getFloat("Price"));
                car.setImages(rs.getString("Images"));
                car.setCapacity(rs.getInt("Capacity"));
                lcar.add(car);
            }
            return lcar;
        } catch (SQLException ex) {
            Logger.getLogger(CarDetailDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<CarDetails> getSearchCar(String carname) {

        List<CarDetails> lcar = new ArrayList<CarDetails>();
        CarDetails car;
        try {

            cs = conn.createConnection().prepareCall("{call sp_SearchByCarName(?)}");
            cs.setString("carname", carname);
            rs = cs.executeQuery();
            while (rs.next()) {
                car = new CarDetails();
                car.setCarID(rs.getInt("CarID"));
                car.setModelID(rs.getInt("ModelID"));
                car.setCarName(rs.getString("CarName"));
                car.setDescription(rs.getString("Description"));
                car.setPrice(rs.getFloat("Price"));
                car.setImages(rs.getString("Images"));
                car.setCapacity(rs.getInt("Capacity"));
                lcar.add(car);
            }
            return lcar;
        } catch (SQLException ex) {
            Logger.getLogger(CarDetailDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<CarDetails> listCarByModelID(int modelID) {
        List<CarDetails> list = new ArrayList<CarDetails>();
        CarDetails car;
        try {
            cs = conn.createConnection().prepareCall("{call sp_ListCarDetailByModel(?)}");
            cs.setInt("model", modelID);
            rs = cs.executeQuery();
            while (rs.next()) {
                car = new CarDetails();
                car.setCarID(rs.getInt("CarID"));
                car.setModelID(rs.getInt("ModelID"));
                car.setCarName(rs.getString("CarName"));
                car.setDescription(rs.getString("Description"));
                car.setPrice(rs.getFloat("Price"));
                car.setImages(rs.getString("Images"));
                car.setCapacity(rs.getInt("Capacity"));
                list.add(car);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(CarDetailDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<CarDetails> searchCarByPrice(float priceFrom, float priceTo) {
        List<CarDetails> list = new ArrayList<CarDetails>();
        CarDetails car;
        try {
            cs = conn.createConnection().prepareCall("{call sp_SearchCarByPrice(?,?)}");
            cs.setFloat("pricefrom", priceFrom);
            cs.setFloat("priceto", priceTo);
            rs = cs.executeQuery();
            while (rs.next()) {
                car = new CarDetails();
                car.setCarID(rs.getInt("CarID"));
                car.setModelID(rs.getInt("ModelID"));
                car.setCarName(rs.getString("CarName"));
                car.setDescription(rs.getString("Description"));
                car.setPrice(rs.getFloat("Price"));
                car.setImages(rs.getString("Images"));
                car.setCapacity(rs.getInt("Capacity"));
                list.add(car);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(CarDetailDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<CarDetails> advanceSearchCar(String carName,float priceFrom, float priceTo) {
        List<CarDetails> list = new ArrayList<CarDetails>();
        CarDetails car;
        try {
            cs = conn.createConnection().prepareCall("{call sp_advanceSearchCar(?,?,?)}");
            cs.setString("carname",carName);
            cs.setFloat("pricefrom", priceFrom);
            cs.setFloat("priceto", priceTo);
            rs = cs.executeQuery();
            while (rs.next()) {
                car = new CarDetails();
                car.setCarID(rs.getInt("CarID"));
                car.setModelID(rs.getInt("ModelID"));
                car.setCarName(rs.getString("CarName"));
                car.setDescription(rs.getString("Description"));
                car.setPrice(rs.getFloat("Price"));
                car.setImages(rs.getString("Images"));
                car.setCapacity(rs.getInt("Capacity"));
                list.add(car);
            }
            return list;
        } catch (SQLException ex) {
            Logger.getLogger(CarDetailDAL.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
}
